package com.pcbly.web.persistence.table;

import java.util.ArrayList;
import java.util.List; 

import org.hibernate.Session;

import com.pcbly.web.bean.table.ColumnBean;
import com.pcbly.web.bean.table.ColumnTempBean;
import com.pcbly.web.bean.table.TableBean;
import com.pcbly.web.components.utils.property.GlobalReadProperties;
import com.pcbly.web.persistence.HibernateBaseDao;

@SuppressWarnings("unchecked")
public class ColumnHibernateDao extends HibernateBaseDao<ColumnBean> implements
		ColumnDao {

	/**
	 * 
	 */
	private static final long serialVersionUID = 7330974868955582773L;

	public ColumnHibernateDao() {

	}

	protected Class<ColumnBean> getEntityClass() {
		return ColumnBean.class;
	}

	@Override
	public void deleteColumnBeans(List<ColumnBean> beans) throws Exception {
		// TODO Auto-generated method stub
		for (ColumnBean bean : beans) {
			getHibernateTemplate().delete(bean);
		}
	}

	@Override
	public void deleteColumnBeansById(String id) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();

		StringBuilder sb = new StringBuilder();
		sb.append("select a.* from t_table a left join t_table_column b ");
		sb.append(" on a.ID=b.TABLEID   ");
		sb.append(" where b.ID in('");
		sb.append(id.replaceAll(",", "','"));
		sb.append("')");
		List<TableBean> tableBeans = session.createSQLQuery(sb.toString())
				.addEntity(TableBean.class).list();

		sb = new StringBuilder();
		sb.append("select * from t_table_column ");
		sb.append(" where ID in('");
		sb.append(id.replaceAll(",", "','"));
		sb.append("')");
		List<ColumnBean> columnBeans = session.createSQLQuery(sb.toString())
				.addEntity(ColumnBean.class).list();
		if (tableBeans.isEmpty()) {
			throw new Exception("delete failure!");
		}
		if (columnBeans.isEmpty()) {
			throw new Exception("delete failure!");
		}
		sb = new StringBuilder();
		try {

			for (TableBean tableBean : tableBeans) {
				for (ColumnBean columnBean : columnBeans) {
					if (columnBean.getTableBean().getId().equals(
							tableBean.getId())) {
						sb.append(" ALTER TABLE   ");
						sb.append(tableBean.getTableNameExtention());
						sb.append(" DROP COLUMN  ");
						sb.append(columnBean.getColumnName());
						sb.append(";");
					}
				}
			}
			session.createSQLQuery(sb.toString()).executeUpdate();

			sb = new StringBuilder();
			sb.append("delete from t_table_column  ");
			sb.append(" where ID in('");
			sb.append(id.replaceAll(",", "','"));
			sb.append("')");
			session.createSQLQuery(sb.toString()).executeUpdate();

		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new Exception(e);

		}
	}

	@Override
	public List<ColumnBean> findColumnBeansFromDatabase(String tableName)
			throws Exception {
		List<ColumnBean> beans = new ArrayList<ColumnBean>();
		try {
			// TODO Auto-generated method stub
			StringBuilder sb = new StringBuilder();
			sb.append("from ColumnTempBean where tableName=?");
			List<ColumnTempBean> list = getSession().createQuery(sb.toString())
					.setString(0, tableName).list();

			for (ColumnTempBean os : list) {
//				 logger.debug(os.getColumnName());
//				 System.out.println(os.getColumnName());
//				
//				 logger.debug(os.getColumnType());
//				 System.out.println(os.getColumnType());
//				
//				 logger.debug(os.getLength());
//				 System.out.println(os.getLength());
//				
//				 logger.debug(os.getColumnNull());
//				 System.out.println(os.getColumnNull());
//				
//				 logger.debug(os.getColumnKey());
//				 System.out.println(os.getColumnKey());
//				
//				 logger.debug(os.getDefaultValue());
//				 System.out.println(os.getDefaultValue());
//				 logger.debug("===============================");
//				 System.out.println("===============================");
				ColumnBean bean = new ColumnBean();
				bean.setColumnName(os.getColumnName());
				bean.setColumnType(os.getColumnType());
				bean.setLength(os.getLength() == null ? 0 : Integer.parseInt(os
						.getLength()));
				bean.setColumnNull(os.getColumnNull());
				bean.setColumnKey(os.getColumnKey());
				bean.setDefaultValue(os.getDefaultValue());
				beans.add(bean);
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new Exception(e);
		}
		// Iterator it=list.iterator();
		// while(it.hasNext()){
		// HashMap<String,String> os=(HashMap<String,String>)it.next();
		// ColumnBean bean=new ColumnBean();
		// bean.setColumnName(os.get("column_name"));
		// bean.setColumnType(os.get("data_type"));
		// if(!(os.get("character_maximum_length")==null)){
		// bean.setLength(Integer.parseInt(os.get("character_maximum_length")));
		// }
		// bean.setColumnNull(os.get("is_nullable"));
		// bean.setColumnKey(os.get("column_key"));
		// bean.setDefaultValue(os.get("column_default"));
		// beans.add(bean);
		// }
//		logger.debug("end-------------------");
//		System.out.println("end-------------------");
		return beans;
	}

	@Override
	public List<ColumnBean> getColumnBeansByTableId(String tableId)
			throws Exception {
		// TODO Auto-generated method stub
		try {
			StringBuilder sbSql = new StringBuilder();
			sbSql.append("from ColumnBean a where a.tableBean.id=?"); 
			// if (!(rb.getString("column.lockColimn").indexOf("0") >= 0)) {
			
			String sb = GlobalReadProperties.getStringToProperty("column.lockColimn");
			if (!(sb == null || "".equals(sb.trim()))) {
				sbSql.append(" and a.columnName not in('");
				sb = sb.replaceAll(",", "','");
				sbSql.append(sb);
				sbSql.append("')");
			}
			// }
			sbSql.append( "  order by a.orderBy  ");
			
			return getSession().createQuery(sbSql.toString()).setString(0,
					tableId).list();
		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new Exception(e);
			// return new ArrayList<ColumnBean>();
		}
	}

	@Override
	public void saveColumnBean(ColumnBean bean) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		try {
			session.save(bean);
			TableBean tableBean = (TableBean) session.createQuery(
					"from TableBean where id=?").setString(0,
					bean.getTableBean().getId()).uniqueResult();
			StringBuilder sb = new StringBuilder();
			sb.append(" alter table ");
			sb.append(tableBean.getTableNameExtention());
			sb.append(" ADD ");
			sb.append(bean.getColumnName().toUpperCase());
			sb.append("  ");
			sb.append(bean.getColumnType());
			if (bean.getColumnType().equals("varchar")) {
				sb.append("(");
				sb.append(bean.getLength() > 4000 ? 3000 : bean.getLength());
				sb.append(")  ");
			}
			if ("NO".equals(bean.getColumnNull())) {
				sb.append(" NOT NULL");
			}
			session.createSQLQuery(sb.toString()).executeUpdate();

		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new Exception(e);

		}
	}

	public void createTableExtention(String tableName) throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" create table ");
		sb.append(tableName);
		sb.append(" (ID varchar(63) NOT NULL,");
		// sb.append(" add index(ID),");
		sb.append("PRIMARY KEY (ID))");
		getSession().createSQLQuery(sb.toString()).executeUpdate();
	}

	@Override
	public void saveColumnBeans(List<ColumnBean> beans) throws Exception {
		// TODO Auto-generated method stub

		for (ColumnBean bean : beans) {
			getSession().save(bean);
		}
	}

	@Override
	public void updateColumnBean(ColumnBean bean) throws Exception {
		// TODO Auto-generated method stub
		
		try {
			
			if(bean.getIsList()==null){
				bean.setIsList("0");
			} 
			if(bean.getIsQuery()==null){
				bean.setIsQuery("0");
			} 
			Session session = getSession();
//			System.out.println(bean.getTableBean().getId());
//			System.out.println(bean.getColumnName());
//			System.out.println(bean.getColumnCName()); 
//			System.out.println(bean.getColumnType());
//			System.out.println(bean.getColumnStyle());
//			System.out.println(bean.getLength());
//			System.out.println(bean.getIsList());
//			System.out.println(bean.getIsQuery());
//			System.out.println(bean.getColumnNull());
//			System.out.println(bean.getDefaultValue()); 
//			System.out.println(bean.getOrderBy());
//			System.out.println(bean.getId());
			
			session.update(bean);
			if (bean.getIflag()!=null&&bean.getIflag().equals("1")) {
				
				TableBean tableBean = (TableBean) session.createQuery(
						"from TableBean where id=?").setString(0,
						bean.getTableBean().getId()).uniqueResult();
				StringBuilder sb = new StringBuilder();
				sb.append("alter table ");
				sb.append(tableBean.getTableNameExtention());
				sb.append(" modify ");
				sb.append(bean.getColumnName());
				sb.append("  ");
				sb.append(bean.getColumnType());
				if (bean.getColumnType().equals("varchar")) {
					sb.append("(");
					sb
							.append(bean.getLength() > 4000 ? 3000 : bean
									.getLength());
					sb.append(")  ");
				}
				if ("NO".equals(bean.getColumnNull())) {
					sb.append(" NOT NULL");
				}
				session.createSQLQuery(sb.toString()).executeUpdate();
			}else{
				TableBean tableBean = (TableBean) session.createQuery(
				"from TableBean where id=?").setString(0,
				bean.getTableBean().getId()).uniqueResult();
				StringBuilder sb = new StringBuilder();
				sb.append("alter table ");
				sb.append(tableBean.getTableName());
				sb.append(" modify ");
				sb.append(bean.getColumnName());
				sb.append("  ");
				sb.append(bean.getColumnType());
				if (bean.getColumnType().equals("varchar")) {
					sb.append("(");
					sb
							.append(bean.getLength() > 4000 ? 3000 : bean
									.getLength());
					sb.append(")  ");
				}
				if ("NO".equals(bean.getColumnNull())) {
					sb.append(" NOT NULL");
				}
				session.createSQLQuery(sb.toString()).executeUpdate();
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	@Override
	public void updateColumnBeans(List<ColumnBean> beans) throws Exception {
		// TODO Auto-generated method stub
		for (ColumnBean bean : beans) {
			getHibernateTemplate().update(bean);
		}
	}

	@Override
	public boolean checkName(String name) throws Exception {
		// TODO Auto-generated method stub
		ColumnBean bean = (ColumnBean) getSession().createQuery(
				"from ColumnBean where upper(columnName)=? ").setString(0,
				name.toUpperCase()).uniqueResult();
		if (bean.getColumnName() == null) {
			return false;
		}
		return bean.getColumnName().equals(name);
	}

	@Override
	public List<ColumnBean> findColumnBeansBytableIds(String tableIds)
			throws Exception {
		// TODO Auto-generated method stub
		List<ColumnBean> beans = new ArrayList<ColumnBean>();
		try {
			// TODO Auto-generated method stub
			StringBuilder sb = new StringBuilder();
			sb.append("select * from t_table_column  where TABLEID in(");
			sb.append("'");
			sb.append(tableIds.replaceAll(",", "','"));
			sb.append("') order by ORDERBY_ ");
			beans = getSession().createSQLQuery(sb.toString()).addEntity(
					ColumnBean.class).list();
		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new Exception(e);
		}
		return beans;
	}

	@Override
	public List<ColumnBean> findColumnBeansByTableName(String tableName)
			throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("from TableBean where tableNameExtention=? ");
		Session session = getSession();
		TableBean tableBean = (TableBean) session.createQuery(sb.toString())
				.setString(0, tableName).uniqueResult();
		sb = new StringBuilder();
		sb
				.append("select * from t_table_column where TABLEID=? and NEWIFLAG='1' order by ORDERBY_ ");
		List<ColumnBean> columnBeans = session.createSQLQuery(sb.toString())
				.addEntity(ColumnBean.class).setString(0, tableBean.getId())
				.list();

		return columnBeans;
	}

}
